package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.FeedBack;

@Repository
public class FeedBackRepository {

	@Autowired
	private DbHelper dbhelper;
	
	@Autowired
	private FeedBackDao feedBackDao;
	
	/**
	 * 创建评估反馈意见空表
	 */
	public String saveFeedBack(FeedBack f){
		this.feedBackDao.save(f);
		return f.getPid();
	}
	
	/**
	 * 查询评估反馈意见表
	 */
	public FeedBack queryFeedBackByExpert(String expertId,String schoolId) throws SQLException{
		String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_school_id as schoolId,a.s_survey as survey,a.s_impression as impression,"
				+" a.s_characteristic as characteristic,a.s_suggest as suggest,a.s_create_time,s.s_name as schoolName "
				+" from t_feedback as a left join t_school as s on a.s_school_id = s.p_id where a.s_expert_id = ? and a.s_school_id = ?";
		return this.dbhelper.getBean(sql, FeedBack.class, expertId,schoolId);
	}
	
	/**
	 * 查询评估反馈意见表
	 */
	public FeedBack queryFeedBackById(String id) throws SQLException{
		String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_school_id as schoolId,a.s_survey as survey,"
			+" a.s_impression as impression,a.s_characteristic as characteristic,a.s_suggest as suggest,"
			+" a.s_create_time as createTime,s.s_name as schoolName "
			+" from t_feedback as a LEFT JOIN t_school as s on a.s_school_id=s.p_id where a.p_id = ?";
		return this.dbhelper.getBean(sql, FeedBack.class, id);
	}
	
	/**
	 * 查询这个专家下的所有表格信息
	 * @throws SQLException 
	 */
	public List<FeedBack> queryFBsByExpert(String expertId) throws SQLException{
		String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_school_id as schoolId,a.s_survey as survey,"
			+" a.s_impression as impression,a.s_characteristic as characteristic,a.s_suggest as suggest,"
			+" a.s_create_time as createTime,s.s_name as schoolName,a.s_state as state "
			+" from t_feedback as a LEFT JOIN t_school as s on a.s_school_id=s.p_id where a.s_expert_id = ?";
		return this.dbhelper.getBeanList(sql, FeedBack.class, expertId);
	}

	public List<FeedBack> queryFeedBackBySchoolId(String schoolId)  throws SQLException{
		String sql = "select a.p_id as pid,a.s_expert_id as expertId,a.s_school_id as schoolId,a.s_survey as survey,a.s_impression as impression,"
				+" a.s_characteristic as characteristic,a.s_suggest as suggest,a.s_create_time,s.s_name as schoolName "
				+" from t_feedback as a left join t_school as s on a.s_school_id = s.p_id where  a.s_school_id = ?";
		return this.dbhelper.getBeanList(sql, FeedBack.class,schoolId);
	}
}
